package com.liu.utils;

import com.liu.domain.student;
import com.liu.repository.StuRepository;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by liu on 17-11-7.
 */
@Service
public class PoiReadExcel {

    @Autowired
    private StuRepository stuRepository;

    /*public static void main(String[] args) {

        File file = new File("/home/liu/Desktop/pp.xls");
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
//            HSSFSheet sheet = workbook.getSheet("Sheet0");
            HSSFSheet sheet = workbook.getSheetAt(0);
            int firstRowNum = 0;
            int lastRowNum = sheet.getLastRowNum();
            for (int i = firstRowNum; i <= lastRowNum;i++) {
                HSSFRow row = sheet.getRow(i);
                //
                int lastCellNum = row.getLastCellNum();
                for (int j = 0;j<lastCellNum;j++) {
                    HSSFCell cell = row.getCell(j);
                    String value = cell.getStringCellValue();
                    System.out.print(value + "");
                }
            }

        }catch (IOException e){
            e.printStackTrace();
        }

    }*/

    public boolean excelDo(InputStream inputStream) {
        boolean ok = false;
        List<List<Object>> stus = new ArrayList<>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
//            HSSFSheet sheet = workbook.getSheet("Sheet0");
            HSSFSheet sheet = workbook.getSheetAt(0);
            int firstRowNum = 0;
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum;i++) {
                HSSFRow row = sheet.getRow(i);
                List<Object> stu = new ArrayList<>();
                int lastCellNum = row.getLastCellNum();
                for (int j = 0;j<lastCellNum;j++) {
                    HSSFCell cell = row.getCell(j);
                    if (cell != null) {
                        if (cell.getCellTypeEnum() == CellType.STRING) {
                            String value = cell.getStringCellValue();
                            stu.add(value);
                            System.out.println(value);
                        }
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                Date value = cell.getDateCellValue();
                                stu.add(value);
                                System.out.println(value);
                            } else {
                                double v = cell.getNumericCellValue();
                                String value = String.valueOf((long) v);
                                stu.add(value);
                                System.out.println(value);
                            }
                        }

                    /*String value = cell.getStringCellValue();
                    System.out.print(value + " ");*/
                        stus.add(stu);
                    }

                }
                stus.add(stu);
            }
            for (List<Object> stu : stus) {
                System.out.println(stu.size());
                if (stu.size() >= 11) {
                    student student = new student();
                    student.setStu_id((String) stu.get(0));
                    student.setRoom_id(Integer.parseInt((String) stu.get(1)));
                    student.setName((String) stu.get(2));
                    student.setSex((String) stu.get(3));
                    student.setCla((String) stu.get(4));
                    student.setPassword((String) stu.get(5));
                    student.setAcad((String) stu.get(6));
                    student.setPro((String) stu.get(7));
                    student.setTime((Date) stu.get(8));
                    student.setBirthday((Date) stu.get(9));
                    student.setPhone((String) stu.get(10));
                    stuRepository.save(student);
                }

            }
            ok = true;
        }catch (IOException e){
            e.printStackTrace();
        }
        return ok;
    }
}
